<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@ page language="java" import="java.sql.*"%>
<%
	Class.forName("org.postgresql.Driver");
	Connection conn = DriverManager.getConnection(
			"jdbc:postgresql://localhost:5432/cse132b", "cse132blogin",
			"1234567890");
	Statement stmt, stmt1, stmt2;

	if (request.getParameter("ACTION") != null) {
		if (request.getParameter("ACTION").equals("Insert")) {
			int Key = Integer.valueOf(request.getParameter("PARAMETER"));
			int Key1 = Integer.valueOf(request.getParameter("PARAMETER1"));

			PreparedStatement statement = conn
					.prepareStatement("INSERT INTO enroll(section_id, student_id, grade_letter, units) VALUES(?,?,?,?)");
			
			
			statement.setInt(1, Key);
			statement.setInt(2, Key1);
			String s = request.getParameter("txtGrade_" + String.valueOf(Key) + String.valueOf(Key1));
			
			if (!s.equals(""))
				statement.setString(3, s);
			else
				statement.setNull(3, java.sql.Types.VARCHAR);
			
			statement.setInt(4, Integer.valueOf(request.getParameter("txtUnit_" + String.valueOf(Key) + String.valueOf(Key1))));
			statement.executeUpdate();
			
			/*
			out.print(Key);
			out.print("<br>");
			out.print(Key1);
			out.print("<br>");
			*/
			statement.close();
		}
		
		if (request.getParameter("ACTION").equals("Update")) {
			int Key = Integer.valueOf(request.getParameter("PARAMETER"));
			int Key1 = Integer.valueOf(request.getParameter("PARAMETER1"));

			PreparedStatement statement = conn
					.prepareStatement("UPDATE enroll SET grade_letter = ? WHERE section_id = ? AND student_id = ?");

			statement.setString(1, request.getParameter("txtGrade_" + String.valueOf(Key) + String.valueOf(Key1)));
			statement.setInt(2, Key);
			statement.setInt(3, Key1);
			statement.executeUpdate();
			statement.close();
		}

	}
%>
<!DOCTYPE html>
<html>
<head>
<script type="text/javascript">
	function doSomething(control) {
		document.getElementById("myForm").submit();
	}
	function Insert(key, key1) {
		document.getElementById("ACTION").value = "Insert";
		document.getElementById("PARAMETER").value = key;
		document.getElementById("PARAMETER1").value = key1;
	}
	function Update(key, key1) {
		document.getElementById("ACTION").value = "Update";
		document.getElementById("PARAMETER").value = key;
		document.getElementById("PARAMETER1").value = key1;
	}

</script>
</head>
<body>
	<table border="1">
		<tr>
			<td valign="top" width="40%">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="/menu.html" />
			</td>
			<td>
				<form id="myForm" method="post" action="pastcourse.jsp">
					<p align="center">
						<font size="6">Classes taken in the Past</font>
					</p>
					<table border="1">

						<%---------- Add ----------%>
						<tr>
							<td>Student Name:</td>
							<td><select name="sltStudentName" id="sltStudentName"
								onchange="javascript:doSomething(this);">
									<option value="0">Select a student</option>
									<%
										stmt = conn.createStatement();
										ResultSet rsStudent = stmt.executeQuery("SELECT * FROM students");
										int selectStudent = 0;
										if (request.getParameter("sltStudentName") != null) {
											selectStudent = Integer.parseInt(request
													.getParameter("sltStudentName"));
										}
										while (rsStudent.next()) {
											if (rsStudent.getInt("student_id") == selectStudent) {
									%>
									<option selected value="<%=rsStudent.getInt("student_id")%>"><%=rsStudent.getString("first_name") + " "
							+ rsStudent.getString("middle_name") + " "
							+ rsStudent.getString("last_name")%></option>
									<%
										} else {
									%>
									<option value="<%=rsStudent.getInt("student_id")%>"><%=rsStudent.getString("first_name") + " "
							+ rsStudent.getString("middle_name") + " "
							+ rsStudent.getString("last_name")%></option>
									<%
										}

										}
										rsStudent.close();
										stmt.close();
									%>
							</select></td>
							</tr>
							<tr>
							<td>Course:</td>
							<td><select name="sltCourses" id="sltCourses"
								onchange="javascript:doSomething(this);">
									<option value="0">Select a course</option>
									<%
										stmt = conn.createStatement();
										ResultSet rsCourses = stmt.executeQuery("SELECT * FROM courses");
										int selectCourses = 0;
										if (request.getParameter("sltCourses") != null) {
											selectCourses = Integer.parseInt(request
													.getParameter("sltCourses"));
										}
										while (rsCourses.next()) {
											if (rsCourses.getInt("course_id") == selectCourses) {
									%>
									<option selected value="<%=rsCourses.getInt("course_id")%>"><%=rsCourses.getString("course_number")%></option>
									<%
										} else {
									%>
									<option value="<%=rsCourses.getInt("course_id")%>"><%=rsCourses.getString("course_number")%></option>
									<%
										}
										}
										rsCourses.close();
										stmt.close();
									%>
							</select></td>
							</tr>
							<tr>
							<td>Quarter:</td>
							<td><select name="sltQuarter" id="sltQuarter" onchange="javascript:doSomething(this);">
									<option value="0">Start Quarter</option>
									<%
									int selectQuarter = 0;
									if (request.getParameter("sltQuarter") != null) {
										selectQuarter = Integer.parseInt(request
												.getParameter("sltQuarter"));
									}
									if (selectQuarter == 1)
									{
									%>
									<option selected value="1">Fall</option>
									<%
									} else {
									%>
									<option value="1">Fall</option>
									<%
									}								
									if (selectQuarter == 2)
									{
									%>
									<option selected value="2">Winter</option>
									<%
									} else {
									%>
									<option value="2">Winter</option>
									<%
									}							
									if (selectQuarter == 3)
									{
									%>
									<option selected value="3">Spring</option>
									<%
									} else {
									%>
									<option value="3">Spring</option>
									<%
									}					
									if (selectQuarter == 4)
									{
									%>
									<option selected value="4">Summer</option>
									<%
									} else {
									%>
									<option value="4">Summer</option>
									<%
									}
									%>

							</select></td>
						</tr>
					</table>
					<table border="1">
					<tr>
							<th>Section ID</th>
							<th>Title</th>
							<th>Year</th>
							<th>Units</th>
							<th>Grade</th>
							<th>Action</th>
						</tr>
					<%
					stmt = conn.createStatement();
					ResultSet rsPassedCourse = stmt
							.executeQuery("SELECT * FROM classes, courses, enroll WHERE classes.course_id = courses.course_id AND enroll.section_id = classes.class_id AND student_id="+ Integer.valueOf(selectStudent).toString()+" AND classes.course_id="+ Integer.valueOf(selectCourses).toString()+" AND quarter='"+ String.valueOf(selectQuarter)+"' ORDER BY enroll.section_id");
					int rsCount = 0;
					String s;
					while (rsPassedCourse.next()) {
						rsCount++;
						s = rsPassedCourse.getString("grade_letter");
						if (s==null) s = "";
					%>
					<tr>
					<td align="center"><%=rsPassedCourse.getInt("section_id")%>				
					</td>
					<td align="center"><%=rsPassedCourse.getString("title")%>				
					</td>
					<td align="center"><%=rsPassedCourse.getInt("year")%>				
					</td>
					<td align="center"><%=rsPassedCourse.getInt("units")%>				
					</td>
					<td align="center"><input size="2" type="text"
								name="txtGrade_<%=rsPassedCourse.getInt("section_id")%><%=rsPassedCourse.getInt("student_id")%>"
								id="txtGrade_<%=rsPassedCourse.getInt("section_id")%><%=rsPassedCourse.getInt("student_id")%>"
								value="<%=s%>"></td>
					<td><input type="submit" value="Update Grade"
					onclick="Update(<%=rsPassedCourse.getInt("section_id")%>, <%=rsPassedCourse.getInt("student_id")%>)"> </td>
					</tr>
					
					<%
					}
					rsPassedCourse.close();
					stmt.close();
					
					if (rsCount == 0 && selectStudent > 0 && selectCourses > 0 && selectQuarter > 0)
					{
						stmt = conn.createStatement();
						ResultSet rsPassedCourseAdd = stmt
								.executeQuery("SELECT * FROM classes, courses WHERE classes.course_id = courses.course_id AND classes.course_id="+ Integer.valueOf(selectCourses).toString()+" AND quarter='"+ String.valueOf(selectQuarter)+"' ");
						while (rsPassedCourseAdd.next()) {
					%>
					<tr>
	
					
					<td align="center"><%=rsPassedCourseAdd.getInt("class_id")%>				
					</td>
					<td align="center"><%=rsPassedCourseAdd.getString("title")%>				
					</td>
					<td align="center"><%=rsPassedCourseAdd.getInt("year")%>				
					</td>
					<td>
					<input size="2" type="text"
								name="txtUnit_<%=rsPassedCourseAdd.getInt("class_id")%><%=Integer.valueOf(selectStudent)%>"
								id="txtUnit_<%=rsPassedCourseAdd.getInt("class_id")%><%=Integer.valueOf(selectStudent)%>"
								value=""></td>
					<td>
					<input size="2" type="text"
								name="txtGrade_<%=rsPassedCourseAdd.getInt("class_id")%><%=Integer.valueOf(selectStudent)%>"
								id="txtGrade_<%=rsPassedCourseAdd.getInt("class_id")%><%=Integer.valueOf(selectStudent)%>"
								value=""></td>
					
					<td align="center"><input type="submit" value="Insert"
							onclick="Insert(<%=rsPassedCourseAdd.getInt("class_id")%>, <%=Integer.valueOf(selectStudent)%>)"></td>
							</tr>
					<%
						}
						rsPassedCourseAdd.close();
						stmt.close();
					}
					%>
					</table>
					<input type="hidden" id="ACTION" name="ACTION" value="None">
					<input type="hidden" id="PARAMETER" name="PARAMETER" value="">
					<input type="hidden" id="PARAMETER1" name="PARAMETER1" value="">
				</form>
			</td>
		</tr>
	</table>
</body>
</html>
<%
	// Close the Connection
	conn.close();
%>